package com.example.tmxk.dao;

import com.example.tmxk.bean.NewsBean;
import com.example.tmxk.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NoticeDao {
    static DBConnection DBConn = new DBConnection();

    public static List<NewsBean> getList(int page) {
        Connection conn = DBConn.getConnection();
        String sql = "select * from Notice limit " + (page - 1) * 10 + ",10";
        List<NewsBean> list = new ArrayList<NewsBean>();
        list = null;
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                NewsBean NewsBean = new NewsBean();
                NewsBean.setNewsID(rs.getInt("NewsID"));
                NewsBean.setNewsTitle(rs.getString("NewsTitle"));
                NewsBean.setNewsContent(rs.getString("NewsContent"));
                NewsBean.setNewsTime(rs.getString("NewsTime"));
                NewsBean.setAdminName(rs.getString("AdminName"));
                list.add(NewsBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加新闻
     *
     * @param NewsBean
     * @return
     */

    public int add(NewsBean NewsBean) {
        Connection conn = DBConn.getConnection();
        String sql = "insert into news values(null,?,?,?,?)";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, NewsBean.getNewsTitle());
            pmst.setString(2, NewsBean.getNewsContent());
            pmst.setString(3, (new java.util.Date()).toString());
            pmst.setString(4, NewsBean.getAdminName());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int update(NewsBean NewsBean) {
        Connection conn = DBConn.getConnection();
        String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=?,AdminName=? where NewsID=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, NewsBean.getNewsTitle());
            pmst.setString(2, NewsBean.getNewsContent());
            pmst.setString(3, (new Date()).toString());
            pmst.setString(4, NewsBean.getAdminName());
            pmst.setInt(5, NewsBean.getNewsID());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int delete(int newsID) {
        Connection conn = DBConn.getConnection();
        try {
            String sql = "delete from news where NewsID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, newsID);
            return pmst.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public static int count(){
        Connection conn = DBConn.getConnection();
        try{
            String sql = "select count(*) from news";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if(rs.next()){
                return rs.getInt(1); // count查询返回一行数据 第一行第一列就是总条数
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
}
